OpenEdge Data Management:<br />SQL Reference REVOKE

Release 10.1A: OpenEdge Data Management:
SQL Reference

REVOKE

Revokes various privileges from the specified users of the database. There are two forms of the REVOKE statement:

Revoke database-wide privileges, either system administration (DBA), general creation (RESOURCE), audit administration (AUDIT_ADMIN), audit archive (AUDIT_ARCHIVE), or audit insert (AUDIT_INSERT).

Revoke various privileges on specific tables and views.

This is the syntax to REVOKE database-wide privileges:

Syntax
REVOKE  { RESOURCE , DBA, AUDIT_ADMIN, AUDIT_ARCHIVE, AUDIT_INSERT}   
   FROM { username [ , username ] , ... } 
   [ RESTRICT | CASCADE ]; 
RESOURCE
Revokes from the specified users the privilege to issue CREATE statements.
DBA
Revokes from the specified users the privilege to create, access, modify, or delete any database object, and revokes the privilege to grant other users any privileges.
AUDIT_ADMIN
Revokes from the specified users the privilege to administrate and maintain a database auditing system.
AUDIT_ARCHIVE
Revokes from the specified users the privilege to read and delete audit records.
AUDIT_INSERT
Revokes from the specified users the privilege to insert application audit records.
FROM username [ , username ] , ...
Revokes the specified privileges on the table or view from the specified list of users.
RESTRICT | CASCADE
Prompts SQL to check to see if the privilege being revoked was passed on to other users. This is possible only if the original privilege included the WITH GRANT OPTION clause. If so, the REVOKE statement fails and generates an error. If the privilege was not passed on, the REVOKE statement succeeds.

If the REVOKE statement specifies CASCADE, revoking the access privileges from a user also revokes the privileges from all users who received the privilege from that user.

If the REVOKE statement specifies neither RESTRICT nor CASCADE, the behavior is the same as for CASCADE.
Note: CASCADE is not supported for AUDIT_ADMIN, AUDIT_ARCHIVE, and AUDIT_INSERT privileges. The only user who can revoke an audit privilege is the user who granted it.
Example

In this example, the audit administration privilege is revoked from bsmith:
REVOKE AUDIT_ADMIN FROM bsmith RESTRICT; 
This is the syntax to REVOKE privileges on specific tables and views:

Syntax
REVOKE [ GRANT OPTION FOR ] 
   { privilege [, privilege ] , ...  | ALL [ PRIVILEGES ] } 
   ON table_name 
   FROM { username [ , username ] , ... | PUBLIC } 
    [ RESTRICT | CASCADE ] ; 
GRANT OPTION FOR
Revokes the GRANT option for the privilege from the specified users. The actual privilege itself is not revoked. If specified with RESTRICT, and the privilege is passed on to other users, the REVOKE statement fails and generates an error. Otherwise, GRANT OPTION FOR implicitly revokes any privilege the user might have given to other users.
privilege
This is the syntax for the privilege item:
Syntax
{ SELECT | INSERT | DELETE | INDEX  
  | UPDATE [ ( column , column , ... ) ] 
  | REFERENCES [ ( column , column , ... ) ] } ; 
privilege [ , privilege ] , ... | ALL [ PRIVILEGES ]
List of privileges to be revoked. See the description in the GRANT statement. Revoking RESOURCE and DBA privileges can only be done by the administrator or a user with DBA privileges.

If more than one user grants access to the same table to a user, then all the grantors must perform a revoke for the user to lose access to the table.

Using the keyword ALL revokes all the privileges granted on the table or view.
FROM PUBLIC
Revokes the specified privileges on the table or view from any user with access to the system.
RESTRICT | CASCADE
Prompts SQL to check to see if the privilege being revoked was passed on to other users. This is possible only if the original privilege included the WITH GRANT OPTION clause. If so, the REVOKE statement fails and generates an error. If the privilege was not passed on, the REVOKE statement succeeds.

If the REVOKE statement specifies CASCADE, revoking the access privileges from a user also revokes the privileges from all users who received the privilege from that user.

If the REVOKE statement specifies neither RESTRICT nor CASCADE, the behavior is the same as for CASCADE.
Example
REVOKE INSERT ON customer FROM dbuser1 ; 
REVOKE DELETE ON cust_view FROM dbuser2 ; 
Note: If the username specified in a GRANT DBA or GRANT RESOURCE operation does not already exist, the GRANT statement creates a row in the SYSDBAUTH system table for the new username. This row is not deleted by a subsequent REVOKE operation.

Authorization

Must have the DBA privilege or ownership of the table (to revoke privileges on a table). To revoke audit privileges, the user must have the DBA privilege or AUDIT ADMINISTRATION WITH GRANT privilege and be the user who granted the audit privilege.

Related statements

GRANT